2
2
.
.
1
1
5
5
.
.
1
1
N
N
a
a
t
t
i
i
v
v
e
e
Q
Q
u
u
e
e
r
r
y
y
I
I
n
n
f
f
o
o
[
[
G
G
]
]
[
[
R
R
]
]
This tutorial shows how to use @Query Annotation to execute Native SQL.
You simply add @Query to a Method and when Method is called it will execute specified SQL and return its result.
You also need to add nativeQuery = true Property because by default Spring expects JPQL.
If SQL has Parameters, Method will also have Input Parameters
● Indexed Parameters (?1, ?2) => ( String name, int age)
● Named Parameters (:parname, :parage) => (@Param("parname") String name, @Param("parage") int age)
To demonstrate this functionality we will load some Persons into DB and the use different SQLs to get them from the DB.
Application Schema [Results]
Spring Boot Starters
GROUP
DEPENDENCY
DESCRIPTION
Web
Spring Web
Enables @RequestMapping (includes Tomcat Server)
SQL
Spring Data JPA
Enables @Entity and @Id
SQL
PostgreSQL Database
Enables Hibernate to work with PostgreSQL DB
Developer Tools
Lombok
Enables @Data which generate helper methods (setters, getters, ...)
MyController
http://localhost:8080/GetJohn
getJohn()
Person
PersonRepository
PostgreSQL
LoadPersons
P
P
r
r
o
o
c
c
e
e
d
d
u
u
r
r
e
e
Create Project: springboot_db_query_native (add Spring Boot Starters from the table)
Edit File: application.properties (enter DB connection parameters)
Create Package: entities (inside main package)
– Create Java Class: Person.java (inside package entities)
Create Package: repositories (inside main package)
– Create Java Interface: PersonRepository.java (inside package repositories)
Create Package: runners (inside main package)
– Create Java Interface: LoadPersons.java (inside package runners)
Create Package: controllers (inside main package)
– Create Java Class: MyController.java (inside package controllers)
application.properties
# POSTGRESQL DATABASE
spring.datasource.url = jdbc:postgresql://localhost:5432/postgres
spring.datasource.username = postgres
spring.datasource.password = letmein
spring.datasource.driver-class-name = org.postgresql.Driver
# JPA / HIBERNATE
spring.jpa.hibernate.ddl-auto = create-drop
Person.java
package com.ivoronline.springboot_db_query_native.entities;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer id;
public String name;
public Integer age;
//CONSTRUCTORS
public Person() { } //Forced by @Entity
public Person(String name, Integer age) { //To simplify PersonLoader
this.name = name;
this.age = age;
}
}
PersonRepository.java
package com.ivoronline.springboot_db_query_native.repositories;
import com.ivoronline.springboot_db_query_native.entities.Person;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface PersonRepository extends CrudRepository<Person, Integer> {
//=======================================================================================
// SELECT
//=======================================================================================
//NO PARAMETERS
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = 'John' AND AGE = 20")
Person getJohn();
//INDEXED PARAMETERS
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = ?1 AND AGE = ?2")
Person selectPersonByNameAgeIndexed(String name, Integer age);
//NAMED PARAMETERS
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = :name AND AGE = :parameterAge")
Person selectPersonByNameAgeNamed(String name, @Param("parameterAge") Integer age);
//RETURN LIST
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = :name")
List<Person> selectPersonsByName(String name);
//=======================================================================================
// UPDATE
//=======================================================================================
@Modifying
@Query(nativeQuery = true, value = "UPDATE PERSON SET AGE = :newAge WHERE NAME = :name")
Integer updatePersonsByName(String name, Integer newAge);
//=======================================================================================
// DELETE
//=======================================================================================
@Modifying
@Query(nativeQuery = true, value = "DELETE FROM PERSON WHERE NAME = :name")
Integer deletePersonsByName(String name);
//=======================================================================================
// INSERT
//=======================================================================================
@Modifying
@Query(nativeQuery = true, value = "INSERT INTO PERSON (name, age) VALUES (:name, :age)")
Integer insertPerson(String name, Integer age);
}
LoadPersons.java
package com.ivoronline.springboot_db_query_native.runners;
import com.ivoronline.springboot_db_query_native.entities.Person;
import com.ivoronline.springboot_db_query_native.repositories.PersonRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
@Component
@Order(1)
public class LoadPersons implements CommandLineRunner {
@Autowired PersonRepository personRepository;
@Override
@Transactional
public void run(String... args) throws Exception {
personRepository.save(new Person("John" , 20));
personRepository.save(new Person("John" , 21));
personRepository.save(new Person("Bill" , 30));
personRepository.save(new Person("Nancy", 40));
personRepository.save(new Person("Susan", 50));
}
}
MyController.java
package com.ivoronline.springboot_db_query_native.controllers;
import com.ivoronline.springboot_db_query_native.entities.Person;
import com.ivoronline.springboot_db_query_native.repositories.PersonRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.List;
@Controller
public class MyController {
@Autowired PersonRepository personRepository;
//================================================================
// SELECT JOHN
//================================================================
@ResponseBody
@RequestMapping("/SelectJohn")
public Person selectJohn() {
Person john = personRepository.getJohn();
return john;
}
//================================================================
// SELECT PERSON BY NAME AGE INDEXED
//================================================================
@ResponseBody
@RequestMapping("/SelectPersonByNameAgeIndexed")
public Person selectPersonByNameAgeIndexed(@RequestParam String name, @RequestParam Integer age) {
Person person = personRepository.selectPersonByNameAgeIndexed(name, age);
return person;
}
//================================================================
// SELECT PERSON BY NAME AGE NAMED
//================================================================
@ResponseBody
@RequestMapping("/SelectPersonByNameAgeNamed")
public Person selectPersonByNameAgeNamed(@RequestParam String name, @RequestParam Integer age) {
Person person = personRepository.selectPersonByNameAgeNamed(name, age);
return person;
}
//================================================================
// SELECT PERSONS BY NAME
//================================================================
@ResponseBody
@RequestMapping("/SelectPersonsByName")
public List<Person> selectPersonsByName(@RequestParam String name) {
List<Person> persons = personRepository.selectPersonsByName(name);
return persons;
}
//================================================================
// UPDATE PERSON BY NAME
//================================================================
@ResponseBody
@Transactional
@RequestMapping("/UpdatePersonsByName")
public String updatePersonsByName(@RequestParam String name, @RequestParam Integer newAge) {
Integer recordsUpdated = personRepository.updatePersonsByName(name, newAge);
return recordsUpdated + " Records updated";
}
//================================================================
// DELETE PERSON BY NAME
//================================================================
@ResponseBody
@Transactional
@RequestMapping("/DeletePersonsByName")
public String deletePersonsByName(@RequestParam String name) {
Integer recordsDeleted = personRepository.deletePersonsByName(name);
return recordsDeleted + " Records deleted";
}
//=======================================================================================
// INSERT IS NOT SUPPORTED BY JPA
//=======================================================================================
@ResponseBody
@Transactional
@RequestMapping("/InsertPerson")
public String insertPerson(@RequestParam String name, @RequestParam Integer age) {
Integer recordsDeleted = personRepository.insertPerson(name, age);
return recordsDeleted + " Records inserted";
}
}
R
R
e
e
s
s
u
u
l
l
t
t
s
s
SELECT http://localhost:8080/SelectJohn (No Parameters)
SELECT http://localhost:8080/SelectPersonByNameAgeIndexed?name=John&age=20 (Indexed Parameters)
SELECT http://localhost:8080/SelectPersonByNameAgeNamed?name=John&age=20 (Named Parameters)
SELECT http://localhost:8080/SelectPersonsByName?name=John (Return List)
SELECT http://localhost:8080/SelectPersonsByNameSorted?name=John (Sort by Age)
UPDATE http://localhost:8080/UpdatePersonsByName?name=John&newAge=100
DELETE http://localhost:8080/DeletePersonsByName?name=John
INSERT http://localhost:8080/InsertPerson?name=Peter&age=60
Application Structure
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
